Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Namespace sk.DAL.SQLProviders
    Public Class SQLProductProvider
        Inherits ProductProvider

        Public Overrides Function GetAllProducts() As System.Collections.Generic.List(Of Product)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Products_GetAllProducts", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetProductCollectionFromReader(reader)

                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Product)
            End Try
        End Function

        Public Overrides Function GetAllProductsByCategory() As System.Collections.Generic.List(Of Product)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Products_GetAllProductsByCategory", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetProductCollectionFromReader(reader)

                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Product)
            End Try
        End Function

        Public Overrides Function CreateProduct(ByVal Prod As Product) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Products_CreateProduct", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pASIN As New SqlParameter("@ASIN", SqlDbType.NVarChar)
                    pASIN.Value = GetNextID()

                    Dim pTitle As New SqlParameter("@Title", SqlDbType.NVarChar)
                    pTitle.Value = Prod.Title

                    Dim pDescription As New SqlParameter("@Description", SqlDbType.Text)
                    pDescription.Value = Prod.Description

                    Dim pCategoryID As New SqlParameter("@CategoryID", SqlDbType.SmallInt)
                    pCategoryID.Value = Prod.CategoryID

                    cmd.Parameters.Add(pASIN)
                    cmd.Parameters.Add(pTitle)
                    cmd.Parameters.Add(pDescription)
                    cmd.Parameters.Add(pCategoryID)

                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function GetProductByID(ByVal ASIN As String) As Product
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Products_GetProductByID", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pASIN As New SqlParameter("@ASIN", SqlDbType.VarChar)
                    pASIN.Value = ASIN
                    cmd.Parameters.Add(pASIN)
                    con.Open()

                    Dim reader As SqlDataReader = cmd.ExecuteReader()
                    reader.Read()

                    Dim newProduct As New Product

                    newProduct.ASIN = reader.GetValue(0)
                    newProduct.Title = reader.GetString(1)
                    newProduct.Description = reader.GetString(2)
                    newProduct.CategoryID = CInt(reader.GetValue(3))


                    Return newProduct
                End Using
            Catch ex As Exception
                Return New Product
            End Try
        End Function

        Public Overrides Function UpdateProduct(ByVal Prod As Product) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Products_UpdateProduct", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pASIN As New SqlParameter("@ASIN", SqlDbType.NVarChar)
                    pASIN.Value = Prod.ASIN

                    Dim pTitle As New SqlParameter("@Title", SqlDbType.NVarChar)
                    pTitle.Value = Prod.Title

                    Dim pDescription As New SqlParameter("@Description", SqlDbType.Text)
                    pDescription.Value = Prod.Description

                    Dim pCategoryID As New SqlParameter("@CategoryID", SqlDbType.SmallInt)
                    pCategoryID.Value = Prod.CategoryID

                    cmd.Parameters.Add(pASIN)
                    cmd.Parameters.Add(pTitle)
                    cmd.Parameters.Add(pDescription)
                    cmd.Parameters.Add(pCategoryID)

                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function


        Public Overrides Function DeleteProduct(ByVal ASIN As String) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Products_DeleteProduct", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pASIN As New SqlParameter("@ASIN", SqlDbType.NVarChar)
                    pASIN.Value = ASIN
                    cmd.Parameters.Add(pASIN)

                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function GetNextID() As Integer
            Try
                Using con As New SqlConnection(Me.ConnectionString)
                    Dim cmd As New SqlCommand("Products_GetNextID", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    con.Open()
                    Dim dr As SqlDataReader
                    dr = cmd.ExecuteReader
                    Dim newId As Integer
                    dr.Read()
                    newId = CInt(dr.GetValue(0))
                    Return newId + 1
                End Using
            Catch ex As Exception
                Return 0
            End Try
        End Function

    End Class
End Namespace

